/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2/9/2011 10:47:56 PM                         */
/*==============================================================*/
CREATE DATABASE HEALTH_CARE

GO
USE  HEALTH_CARE
GO
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DIAGNOSES') and o.name = 'FK_DIAGNOSE_DOCTOR_DI_PATIENT')
alter table DIAGNOSES
   drop constraint FK_DIAGNOSE_DOCTOR_DI_PATIENT
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DIAGNOSES') and o.name = 'FK_DIAGNOSE_DOCTOR_DI_STAFF')
alter table DIAGNOSES
   drop constraint FK_DIAGNOSE_DOCTOR_DI_STAFF
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('MEDICAL_HISTORY') and o.name = 'FK_MEDICAL__FK_STAFF__STAFF')
alter table MEDICAL_HISTORY
   drop constraint FK_MEDICAL__FK_STAFF__STAFF
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PATIENT') and o.name = 'FK_PATIENT_PATIENT_R_SERVICE')
alter table PATIENT
   drop constraint FK_PATIENT_PATIENT_R_SERVICE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAFF') and o.name = 'FK_STAFF_STAFF_HAS_SPECIFIC')
alter table STAFF
   drop constraint FK_STAFF_STAFF_HAS_SPECIFIC
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TEST_SAMPLES') and o.name = 'FK_TEST_SAM_RELATIONS_STAFF')
alter table TEST_SAMPLES
   drop constraint FK_TEST_SAM_RELATIONS_STAFF
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TEST_SAMPLES') and o.name = 'FK_TEST_SAM_RELATIONS_RESOURCE')
alter table TEST_SAMPLES
   drop constraint FK_TEST_SAM_RELATIONS_RESOURCE
go

if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TEST_SAMPLES') and o.name = 'FK_TEST_SAM_RELATIONS_SERVICE')
alter table TEST_SAMPLES
   drop constraint FK_TEST_SAM_RELATIONS_SERVICE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DIAGNOSES')
            and   type = 'U')
   drop table DIAGNOSES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('MEDICAL_HISTORY')
            and   type = 'U')
   drop table MEDICAL_HISTORY
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PATIENT')
            and   type = 'U')
   drop table PATIENT
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RESOURCE')
            and   type = 'U')
   drop table RESOURCE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SERVICE')
            and   type = 'U')
   drop table SERVICE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SPECIALIZATION')
            and   type = 'U')
   drop table SPECIALIZATION
go

if exists (select 1
            from  sysobjects
           where  id = object_id('STAFF')
            and   type = 'U')
   drop table STAFF
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TEST_SAMPLES')
            and   type = 'U')
   drop table TEST_SAMPLES
go

/*==============================================================*/
/* Table: DIAGNOSES                                             */
/*==============================================================*/
create table DIAGNOSES (
   PATIENT_ID           bigint               not null,
   STAFF_ID             int                  not null,
   APPOINT_TIME         datetime             not null,
   RESULT               varchar(400)         null,
   constraint PK_DIAGNOSES primary key nonclustered (PATIENT_ID, STAFF_ID)
)
go

/*==============================================================*/
/* Table: MEDICAL_HISTORY                                       */
/*==============================================================*/
create table MEDICAL_HISTORY (
   MEDICAL_ID           bigint               not null,
   STAFF_ID             int                  not null,
   SELF_DESCRIPTION     varchar(400)         null,
   FAMILY_DESCRIPTION   varchar(400)         null,
   MEDICINE_ALLERGY     varchar(200)         null,
   BLOOD_GROUP          varchar(10)          not null,
   WEIGHT               int                  not null,
   HEIGHT               int                  not null,
   VESSEL               int                  null,
   BLOOD_PRESSURE       varchar(10)          not null,
   constraint PK_MEDICAL_HISTORY primary key nonclustered (MEDICAL_ID)
)
go

/*==============================================================*/
/* Table: PATIENT                                               */
/*==============================================================*/
create table PATIENT (
   PATIENT_ID           bigint               not null,
   SERVICE_ID           int                  null,
   FULL_NAME            varchar(400)         not null,
   DATE_OF_BIRTH        datetime             not null,
   JOB                  varchar(50)          null,
   ADDRESS              varchar(500)         not null,
   PHONE                bigint               null,
   constraint PK_PATIENT primary key nonclustered (PATIENT_ID)
)
go

/*==============================================================*/
/* Table: RESOURCE                                              */
/*==============================================================*/
create table RESOURCE (
   RESOURCE_ID          bigint               not null,
   RESOURCE_NAME        varchar(50)          not null,
   TYPE                 varchar(70)          not null,
   DESCRIPTION          varchar(200)         not null,
   constraint PK_RESOURCE primary key nonclustered (RESOURCE_ID)
)
go

/*==============================================================*/
/* Table: SERVICE                                               */
/*==============================================================*/
create table SERVICE (
   SERVICE_ID           int                  not null,
   SERVICE_NAME         varchar(50)          not null,
   constraint PK_SERVICE primary key nonclustered (SERVICE_ID)
)
go

/*==============================================================*/
/* Table: SPECIALIZATION                                         */
/*==============================================================*/
create table SPECIALIZATION (
   SPECIFICATION_ID     int                  not null,
   SPECIFICATION_NAME   varchar(25)          not null,
   DESCRIPTION          varchar(200)         null,
   constraint PK_SPECIFICATION primary key nonclustered (SPECIFICATION_ID),
   constraint AK_PK_SPECIFICATION_SPECIFIC unique (SPECIFICATION_ID)
)
go

/*==============================================================*/
/* Table: STAFF                                                 */
/*==============================================================*/
create table STAFF (
   STAFF_ID             int                  not null,
   SPECIFICATION_ID     int                  null,
   FULL_NAME            varchar(400)         null,
   DATE_OF_BIRTH        datetime             not null,
   GENDER               char(1)              not null,
   PHONE                bigint               not null,
   ADDRESS              varchar(500)         not null,
   EMAIL                varchar(50)          null,
   IMAGE                varchar(80)          not null,
   ROLE                 varchar(30)          not null,
   TYPE_OF_LABORATORY   varchar(80)          null,
   EXPERIENCE           smallint             null,
   constraint PK_STAFF primary key nonclustered (STAFF_ID)
)
go

/*==============================================================*/
/* Table: TEST_SAMPLES                                          */
/*==============================================================*/
create table TEST_SAMPLES (
   STAFF_ID             int                  not null,
   SERVICE_ID           int                  not null,
   RESOURCE_ID          bigint               not null,
   TEST_NAME            varchar(100)         null,
   TRACKED_DATE         datetime             not null,
   constraint PK_TEST_SAMPLES primary key nonclustered (STAFF_ID, SERVICE_ID, RESOURCE_ID)
)
go

alter table DIAGNOSES
   add constraint FK_DIAGNOSE_DOCTOR_DI_PATIENT foreign key (PATIENT_ID)
      references PATIENT (PATIENT_ID)
go

alter table DIAGNOSES
   add constraint FK_DIAGNOSE_DOCTOR_DI_STAFF foreign key (STAFF_ID)
      references STAFF (STAFF_ID)
go

alter table MEDICAL_HISTORY
   add constraint FK_MEDICAL__FK_STAFF__STAFF foreign key (STAFF_ID)
      references STAFF (STAFF_ID)
go

alter table PATIENT
   add constraint FK_PATIENT_PATIENT_R_SERVICE foreign key (SERVICE_ID)
      references SERVICE (SERVICE_ID)
go

alter table STAFF
   add constraint FK_STAFF_STAFF_HAS_SPECIFIC foreign key (SPECIFICATION_ID)
      references SPECIALIZATION (SPECIFICATION_ID)
go

alter table TEST_SAMPLES
   add constraint FK_TEST_SAM_RELATIONS_STAFF foreign key (STAFF_ID)
      references STAFF (STAFF_ID)
go

alter table TEST_SAMPLES
   add constraint FK_TEST_SAM_RELATIONS_RESOURCE foreign key (RESOURCE_ID)
      references RESOURCE (RESOURCE_ID)
go

alter table TEST_SAMPLES
   add constraint FK_TEST_SAM_RELATIONS_SERVICE foreign key (SERVICE_ID)
      references SERVICE (SERVICE_ID)
go

